﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_BackupFile]
#-- Purpose:		Restores a backup file.  Chooses between
#--					LiteSpeed if required and available, otherwise standard
#--	Last Update:	10/23/2015
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	[master].[dbo].[xp_restore_database]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_BackupFile]
(
	@database_name			sysname,
	@file_name				nvarchar(4000),
	@stopat_time			datetime			= NULL,
	@recover_now			bit					= 0,
	@replace_existing		bit					= 0,
	@verify_only			bit					= 1,
	@get_commands_only		bit					= 0,
	@new_command			nvarchar(MAX)		= NULL OUTPUT
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@sError varchar(1000), @has_rights bit
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spRest_BackupFile'

--- Determine if the user is authorized to do restores
EXEC [dbo].[spRest_IsAllowed] @database_name = @database_name, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0
  BEGIN
	SELECT	@sError = 'You (' + ORIGINAL_LOGIN() + ') are not authorized to restore the database ' + @database_name
	RAISERROR (@sError, 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE	@in_msdb bit, @db_exists bit, @backup_set_id int, @sCommand nvarchar(4000), @sCmd nvarchar(MAX),
		@logical_name sysname, @db_physical_name nvarchar(2000), @backup_type char(1)
DECLARE @tCmd TABLE ([iID] int IDENTITY(1,1) PRIMARY KEY, [sCmd] nvarchar(MAX))
DECLARE @tBkp TABLE
(
	logical_name			sysname,
	db_logical_name			sysname NULL,
	physical_name			nvarchar(2000),
	db_physical_name		nvarchar(2000) NULL,
	rename_logical_name		bit	DEFAULT(0),
	rename_physical_name	bit	DEFAULT(0)
)

--- Don't allow restoring system databases or centraldba_db with this process
IF @database_name IN ('master', 'model', 'msdb', DB_NAME()) AND @verify_only = 0
  BEGIN
	SELECT	@sError = 'This process cannot be used to restore the database ' + QUOTENAME(@database_name)
	RAISERROR (@sError, 16, 1)
  END

--- Validate that the backup file does in fact exist
IF [dbo].[fnIO_FileExists](@file_name) = 0
  BEGIN
	RAISERROR ('The backup file provided either does not exist or cannot be accessed (@backup_file)', 16, 1)
	RETURN
  END

--- Determine if the backup file is known by msdb
SELECT	@backup_set_id = bs.backup_set_id,
		@backup_type = bs.type
FROM	[msdb].[dbo].[backupmediafamily] bmf
JOIN	[msdb].[dbo].[backupset] bs ON bmf.media_set_id = bs.media_set_id
WHERE	bmf.physical_device_name = @file_name

--- Determine if the database exists on this server
SELECT	@db_exists = (CASE	WHEN EXISTS(SELECT TOP 1 * FROM [master].[sys].[databases] WHERE name = @database_name)
							THEN 1 ELSE 0 END)

--- Update the repository of current files in a database
EXEC	[dbo].[spRest_Update_CurrentDbFileNames] @database_name = @database_name

--- Build the command to restore the database
IF EXISTS(select * from [master].[dbo].[sysobjects] where id = object_id(N'[master].[dbo].[xp_backup_database]'))
  BEGIN
	IF @backup_type IN ('D', 'I')
		SELECT	@sCommand = 'EXEC [master].[dbo].[xp_restore_database] @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''
	ELSE
		SELECT	@sCommand = 'EXEC [master].[dbo].[xp_restore_log] @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''

	IF @verify_only = 1
		SELECT	@sCommand = @sCommand + ', @verify=1'
	ELSE
	  BEGIN
		--- If the database doesn't exist, add where to put the new files
		IF @db_exists = 0
			SELECT	@sCommand = @sCommand + ', @with=''move ''''logical_name'''' to ''''physical_name.mdf'''',',
					@sCommand = @sCommand + ', @with=''move ''''logical_name'''' to ''''physical_name.ldf'''' '
		--- ', @with=''replace'' '		--- to restore to an existing database, but files with different names
	  END

	--- Execute Command
	IF @get_commands_only = 1
		INSERT INTO @tCmd VALUES(@sCommand)
	ELSE
	  BEGIN
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)
	  END
  END
ELSE
  BEGIN
	--- Verify the database backup validity
	IF @verify_only = 1 AND @backup_set_id IS NOT NULL
	  BEGIN
		--- Execute the catalog command
		SELECT	@sCommand = 'RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''';'

		IF @get_commands_only = 1
			INSERT INTO @tCmd VALUES(@sCommand)
		ELSE
		  BEGIN
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END
	  END
	--- Catalog the backup and verify it's state
	ELSE IF @backup_set_id IS NULL
	  BEGIN
		--- Execute the catalog command
		SELECT	@sCommand = 'RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''' WITH LOADHISTORY;'
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)

		--- Determine if the backup file is known by msdb, now that it has been inventoried
		SELECT	@backup_set_id = bs.backup_set_id,
				@backup_type = bs.type
		FROM	[msdb].[dbo].[backupmediafamily] bmf
		JOIN	[msdb].[dbo].[backupset] bs ON bmf.media_set_id = bs.media_set_id
		WHERE	bmf.physical_device_name = @file_name
	  END

	--- The backup file failed to inventory
	IF @backup_set_id IS NULL
	  BEGIN
		RAISERROR ('The backup file was not cataloged and an attempt to catalog the file failed (@backup_file)', 16, 1)
		RETURN
	  END

	--- Perform the restore
	IF @verify_only = 0
	  BEGIN
		--- Basic Command
		IF @backup_type IN ('D', 'I')
			SELECT	@sCommand = 'RESTORE DATABASE [' + @database_name + '] FROM DISK = N''' + @file_name + ''' '
		ELSE
			SELECT	@sCommand = 'RESTORE LOG [' + @database_name + '] FROM DISK = N''' + @file_name + ''' '

		--- With or without Recovery
		IF @recover_now = 0
			SELECT	@sCommand = @sCommand + 'WITH NORECOVERY '
		ELSE
			SELECT	@sCommand = @sCommand + 'WITH RECOVERY '

		--- Compare file locations of the database backed up (backupfile) to the destination database
		INSERT INTO @tBkp
			EXEC [dbo].[spRest_GetDbFileNames] @database_name = @database_name, @backup_set_id = @backup_set_id
					
		--- If any file mismatches are found, define all move conditions
		IF EXISTS (SELECT TOP 1 * FROM @tBkp WHERE rename_physical_name = 1)
		  BEGIN
			DECLARE crs CURSOR FOR SELECT logical_name, db_physical_name FROM @tBkp WHERE rename_physical_name = 1
			OPEN crs
			FETCH NEXT FROM crs INTO @logical_name, @db_physical_name 
			WHILE @@FETCH_STATUS <> -1
			  BEGIN
				SELECT	@sCommand = @sCommand + ', MOVE ''' + @logical_name + ''' TO ''' + @db_physical_name + ''''
				FETCH NEXT FROM crs INTO @logical_name, @db_physical_name 
			  END
			CLOSE crs
			DEALLOCATE crs
		  END

		--- Add the hint for a time to stopat
		IF @stopat_time IS NOT NULL AND @backup_type = 'L'
		  BEGIN
			SELECT	@sCommand = @sCommand + ', STOPAT = ''' + CONVERT(varchar(20), @stopat_time) + ''''
		  END

		--- Add the hint to replace existing if passed in
		IF @replace_existing = 1 AND @backup_type = 'D'
		  BEGIN
			SELECT	@sCommand = @sCommand + ', REPLACE'
		  END

		--- Execute Command
		SELECT	@sCommand = @sCommand + ';'

		IF @get_commands_only = 1
			INSERT INTO @tCmd VALUES(@sCommand)
		ELSE
		  BEGIN
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END

		--- If this is the last file to restore and we are entering recovery, check for mismatched internal names.
		---		If found, rename the internal names to match the new database.
		IF @recover_now = 1 AND EXISTS (SELECT TOP 1 * FROM @tBkp WHERE rename_logical_name = 1)
		  BEGIN
			DECLARE @s1 sysname, @s2 sysname
			DECLARE crs CURSOR FOR SELECT logical_name, db_logical_name FROM @tBkp WHERE rename_logical_name = 1
			OPEN crs
			FETCH NEXT FROM crs INTO @s1, @s2
			WHILE @@FETCH_STATUS <> -1
			  BEGIN
				SELECT	@sCommand = 'ALTER DATABASE ' + QUOTENAME(@database_name) + ' MODIFY FILE ( NAME = ''' + @s1 + ''', NEWNAME = ''' + @s2 + ''')'

				IF @get_commands_only = 1
					INSERT INTO @tCmd VALUES(@sCommand)
				ELSE
				  BEGIN
					EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
					EXEC	(@sCommand)
				  END

				FETCH NEXT FROM crs INTO @s1, @s2
			  END
			CLOSE crs
			DEALLOCATE crs
		  END

		--- If this is the last file to restore and we are entering recovery, ensure the database owner is 'sa'
		IF @recover_now = 1
		  BEGIN
			SELECT	@sCommand = 'USE ' + QUOTENAME(@database_name) + '; EXEC sp_changedbowner ''sa'';'

			IF @get_commands_only = 1
				INSERT INTO @tCmd VALUES(@sCommand)
			ELSE
			  BEGIN
				EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
				EXEC	(@sCommand)
			  END
		  END
	  END
  END

IF @get_commands_only = 1
  BEGIN
	SELECT @sCmd = ISNULL(@sCmd + CHAR(10) + CHAR(13), SPACE(0)) + [sCmd] FROM @tCmd ORDER BY [iID]
	SELECT @new_command = @sCmd
  END
ELSE
  BEGIN
	--- Update the repository of current files in a database
	EXEC	[dbo].[spRest_Update_CurrentDbFileNames] @database_name = @database_name
  END

SET NOCOUNT OFF